package cp3120.dao.mysql;

import cp3120.dao.*;
import cp3120.model.Airport;
import cp3120.model.Flight;
import cp3120.model.FlightSearch;
import cp3120.net.ActiveUser;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Vector;

/**
 *
 * @author Adam Scarr <scarr.adam@gmail.com>
 */
public class MySQLFlightDAO implements FlightDAO {
    private Connection db;
    private PreparedStatement getFlight;
    private PreparedStatement getAllFlights;
    private PreparedStatement updateFlight;
    private PreparedStatement addFlight;
    private PreparedStatement deleteFlight;
private PreparedStatement getAirports;
    private PreparedStatement getAirport;
    private PreparedStatement addAirport;
    private PreparedStatement updateAirport;
    private PreparedStatement deleteAirport;
    private Map<Integer, Flight> flightFlyweights = new HashMap<Integer, Flight>();

    /**
     * constructor, takes a database connection and calls private member to prepare the My SQL statements.
      * @param db the database that will be accessed
     */
    public MySQLFlightDAO(Connection db) {
        this.db = db;
        prepareStatements();
    }

    private void prepareStatements() {
        try {
            getFlight = db.prepareStatement("SELECT * FROM flight WHERE flightID = ? ");
            getAllFlights = db.prepareStatement("SELECT * FROM `flight`");
            updateFlight = db.prepareStatement("UPDATE flight " +
                                               "SET depart_loc = ?, " +
                                               "    depart_time = ?, " +
                                               "    arrival_loc = ?, " +
                                               "    arrival_time = ?, " +
                                               "    seats = ?, " +
                                               "    price = ? " +
                                               "WHERE flightID = ?;");
            addFlight = db.prepareStatement(
                            "INSERT INTO flight " +
                            "(depart_loc, depart_time, arrival_loc, arrival_time, seats, price) " +
                            " VALUES (?, ?, ?, ?, ?, ?)");
            deleteFlight = db.prepareStatement("DELETE FROM flight WHERE flightID = ?");
            getAirports = db.prepareStatement("SELECT id, name, tax FROM airport;");
            getAirport = db.prepareStatement("SELECT id, name, tax FROM airport WHERE id = ?");
            updateAirport = db.prepareStatement("UPDATE airport SET name = ?, tax = ? WHERE id = ?");
            deleteAirport = db.prepareStatement("DELETE FROM airport WHERE id = ?");
            addAirport = db.prepareStatement("INSERT INTO airport (name, tax) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS);

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * gets a flight from the database
     * @param flightID The ID of the flight to return
     * @return a flight, else null if the flight could not be retrieved
     */
    public Flight getFlight(int flightID) {
        if (!ActiveUser.hasPermission("View Flights")) return null;

        try {
            getFlight.setInt(1, flightID);
            ResultSet result = getFlight.executeQuery();
            if (result.next()) {
                return buildFlight(result);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    /**
     * gets all flights stored in the database
     * @return a List of Flight objects
     */
    public List<Flight> getFlights() {

        LinkedList<Flight> flights = new LinkedList();

        try {
            ResultSet result = getAllFlights.executeQuery();

            while (result.next()) {
                flights.add(buildFlight(result));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return flights;
    }

    private Flight buildFlight(ResultSet result) throws SQLException {
        if (!flightFlyweights.containsKey(result.getInt("flightID"))) {
            Flight flight =  new Flight(result.getInt("flightID"),
                                        getAirport(result.getInt("depart_loc")),
                                        result.getString("depart_time"),
                                        getAirport(result.getInt("arrival_loc")),
                                        result.getString("arrival_time"),
                                        result.getInt("seats"),
                                        result.getFloat("price"));
            flightFlyweights.put(result.getInt("flightID"), flight);
        }

        return flightFlyweights.get(result.getInt("flightID"));
    }

    private void setVars(PreparedStatement stat, Flight flight) throws SQLException {
        if (flight.getFlightID() != 0) {

            // We wont be handed back our wonderfull flyweight unfortunatly.
            if (flightFlyweights.containsKey(flight.getFlightID())) {
                Flight flyweight = flightFlyweights.get(flight.getFlightID());
                flyweight.setDepartureLocation(flight.getDepartureLocation());
                flyweight.setDepartureTime(flight.getDepartureTime());
                flyweight.setArrivalLocation(flight.getArrivalLocation());
                flyweight.setArrivalTime(flight.getArrivalTime());
                flyweight.setSeats(flight.getSeats());
                flyweight.setPrice(flight.getPrice());
            }

            stat.setInt    (7, flight.getFlightID());
        }
        stat.setInt    (1, flight.getDepartureLocation().getId());
        stat.setString (2, flight.getDepartureTime());
        stat.setInt    (3, flight.getArrivalLocation().getId());
        stat.setString (4, flight.getArrivalTime());
        stat.setInt    (5, flight.getSeats());
        stat.setFloat  (6, flight.getPrice());
    }

    /**
     * replaces a flight in the db with another flight
     * @param flight the flight to be added to the database
     * @return true if the flight was updated, else false
     */
    public boolean updateFlight(Flight flight) {

        try {
            setVars(updateFlight, flight);

            return updateFlight.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * adds a new flight entry to the database
     * @param flight the new flight to be added to the database
     * @return true if the flight was added, else false
     */
    public boolean addFlight(Flight flight) {

        try {
            setVars(addFlight, flight);

            return addFlight.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * deletes a flight from the database
     * @param flight the flight to be deleted
     * @return true if the flight was deleted, else false
     */
    public boolean deleteFlight(Flight flight) {

        try {
            deleteFlight.setInt (1, flight.getFlightID());

            return deleteFlight.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * returns an airport (location) from the database
     * @param airportID the ID of the airport to be retrieved
     * @return an Airport object, else null if the airport could not be retrieved
     */
    public Airport getAirport(int airportID) {

        try {
            getAirport.setInt(1, airportID);
            ResultSet result = getAirport.executeQuery();

            Airport airport = new Airport();
            if (result.next()) {
                airport.setId(result.getInt(1));
                airport.setName(result.getString(2));
                airport.setTax(result.getFloat(3));
                return airport;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    /**
     * gets a list of all airports int he list
     * @return a list of Airports, else null if there was an error
     */
    public List<Airport> getAirports() {

        try {
            ResultSet result = getAirports.executeQuery();

            List airports = new Vector<Airport>();

            while (result.next()) {
                Airport airport = new Airport();
                airport.setId(result.getInt(1));
                airport.setName(result.getString(2));
                airport.setTax(result.getFloat(3));
                airports.add(airport);
            }

            return airports;

        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * replaces an airport in the database with another airport
     * @param airport the airport to be added to the database
     * @return true if the airport was updated, else false
     */
    public boolean updateAirport(Airport airport) {
        if (!ActiveUser.hasPermission("Manage Airports")) return false;

        try {
            updateAirport.setString(1, airport.getName());
            updateAirport.setFloat(2, airport.getTax());
            updateAirport.setInt(3, airport.getId());

            return updateAirport.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * adds a new airport to the database
     * @param airport the airport to be added to the database
     * @return true if the airport was added to the database, else false
     */
    public boolean addAirport(Airport airport) {

        try {
            addAirport.setString(1, airport.getName());
            addAirport.setFloat(2, airport.getTax());
            boolean status = addAirport.execute();

            ResultSet rs = addAirport.getGeneratedKeys();
            if (rs != null && rs.next()) {
                airport.setId(rs.getInt(1));
            }

            return status;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * deletes an airport from the database
     * @param airport the airport to be deleted
     * @return true if the airport was deleted, else false
     */
    public boolean deleteAirport(Airport airport) {

        try {
            deleteAirport.setInt(1, airport.getId());
            return deleteAirport.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * searchs the database for all flights matching the given search criteria
     * @param search contains the paramaters that are being searched
     * @return a list of mathing flights, else null if no reutls are found. If the active user does not have permission
     * then an empty vector is returned.
     */
    public List<Flight> searchFlights(FlightSearch search) {

        try {
            Statement stat = db.createStatement();
            String query = "SELECT flightID, depart_loc, depart_time, arrival_loc, arrival_time, seats, price FROM flight WHERE 1";

            if (search.getDepartureLocation() != null) {
                query += " AND depart_loc = " + search.getDepartureLocation().getId();
            }

            if (search.getArrivalLocation() != null) {
                query += " AND arrival_loc = " + search.getArrivalLocation().getId();
            }

            if (search.getDepartureDate() != null) {
                query += " AND DATE(depart_time) = '" + search.getDepartureDate() + "'";
            }

            if (search.getArrivalDate() != null) {
                query += " AND DATE(arrival_time) = '" + search.getArrivalDate() + "'";
            }

            System.out.println(query);

            ResultSet resultSet = stat.executeQuery(query);
            List<Flight> result = new Vector<Flight>();
            while (resultSet.next()) {
                result.add(buildFlight(resultSet));
            }
            return result;
        } catch (SQLException ex) {
            return null;
        }
    }
}
